package com.wanmait.travel.dao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.travel.util.DbHelper;
import com.wanmait.travel.util.ShopCar;
import com.wanmait.travel.vo.Scenic;
import com.wanmait.travel.vo.Userinfo;

public class UserinfoDAO {
	private static UserinfoDAO userinfoDAO;
	private UserinfoDAO() {}
	public static UserinfoDAO getDAO() {
		if(userinfoDAO==null) {
			userinfoDAO =new UserinfoDAO();
		}
		return userinfoDAO;
	}
	private List<Userinfo> findBySql(String sql, List params){
		ArrayList<Userinfo> userinfos=new ArrayList<Userinfo>();
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			for (int i = 0; params!=null&&i<params.size(); i++) {
				ps.setObject(i+1,params.get(i) );
			}
			rs=ps.executeQuery();
			while(rs.next())
			{
				Userinfo userinfo=new Userinfo();
				userinfo.setId(rs.getInt("id"));
				userinfo.setUsername(rs.getString("username"));
				userinfo.setUserpass(rs.getString("userpass"));
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setGender(rs.getString("gender"));
				userinfo.setPhone(rs.getString("phone"));
				userinfo.setPic(rs.getString("pic"));
				userinfo.setBirthday(rs.getDate("birthday"));
				userinfo.setRealName(rs.getString("real_name"));
				userinfo.setAddress(rs.getString("address"));
				userinfo.setJoinDate(rs.getTimestamp("join_date"));
				userinfo.setLandCount(rs.getInt("land_count"));
				userinfo.setTen(rs.getDouble("ten"));
				userinfo.setLatitude(rs.getDouble("latitude"));
				userinfo.setEnable(rs.getBoolean("enable"));
				userinfos.add(userinfo);	
			}		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return userinfos;
	}
	public void  insert(Userinfo userinfo)
	{
		String sql ="insert into userinfo(username,userpass,nickname,gender,phone,pic,birthday,real_name,address)values(?,?,?,?,?,?,?,?,?)";
		ArrayList params =new ArrayList();
		params.add(userinfo.getUsername());
		params.add(userinfo.getUserpass());
		params.add(userinfo.getNickname());
		params.add(userinfo.getGender());
		params.add(userinfo.getPhone());
		params.add(userinfo.getPic());
		params.add(userinfo.getBirthday());
		params.add(userinfo.getRealName());
		params.add(userinfo.getAddress());
		DbHelper.update(sql,params);
	}
	
	
	public Userinfo findByNameAndPass(String userName, String userPass)
	{
		Userinfo userinfo =null;

		String sql ="SELECT *FROM userinfo WHERE username=?AND userpass=?";

		Connection conn =DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs= null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setString(1, userName);
			ps.setString(2,userPass);
			rs= ps.executeQuery();
			if(rs.next()) {
				userinfo =new Userinfo();
				userinfo.setId(rs.getInt("id"));
				userinfo.setUsername(userName);
				userinfo.setUserpass(userPass);
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setGender(rs.getString("gender"));
				userinfo.setPhone(rs.getString("phone"));
				userinfo.setPic(rs.getString("pic"));
				userinfo.setBirthday(rs.getDate("birthday"));
				userinfo.setRealName(rs.getString("real_name"));
				userinfo.setAddress(rs.getString("address"));
				userinfo.setJoinDate(rs.getTimestamp("join_date"));
				
			}
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return userinfo;
	}

	public Userinfo findById(int id){
		String sql="select * from userinfo where id=? ";
		ArrayList params=new ArrayList();
		params.add(id);
		List<Userinfo> userinfos= this.findBySql(sql, params);
		
		
		
		Userinfo userinfo=userinfos.get(0);
		
		return userinfo;
	
	}
    public void update(Userinfo userinfo)
    {
    	String sql= "UPDATE userinfo set username=?,userpass=?,nickname=?,gender=?,phone=?,pic=?,birthday=?,real_name=?,address=? WHERE id=?";
    	ArrayList params=new ArrayList();
    	params.add(userinfo.getUsername());
    	params.add(userinfo.getUserpass());
    	params.add(userinfo.getNickname());
    	params.add(userinfo.getGender());
    	params.add(userinfo.getPhone());
    	params.add(userinfo.getPic());
    	params.add(userinfo.getBirthday());
    	params.add(userinfo.getRealName());
    	params.add(userinfo.getAddress());
    	params.add(userinfo.getId());
    	DbHelper.update(sql,params);
    }
    public Userinfo findByName(String username,String phone,String realName)
    {
    	Userinfo userinfo=null;
    	String sql="select * from userinfo where username=? and phone=? AND real_name=?";
		Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			ps=conn.prepareStatement(sql);
			
			ps.setString(1, username);
			ps.setString(2, phone);
			ps.setString(3, realName);
			rs=ps.executeQuery();
			if(rs.next())
			{
				userinfo=new Userinfo();
				userinfo.setId(rs.getInt("id"));
				userinfo.setUsername(rs.getString("username"));
				userinfo.setPhone(rs.getString("phone"));
				userinfo.setRealName(rs.getString("real_name"));
				
			}		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return userinfo;
		
	}
    public void updatePassword(int id,String userpass)
    {
    	String sql="update userinfo set userpass=? where id=?";
    	ArrayList params=new ArrayList();
    	params.add(userpass);
    	params.add(id);
    	DbHelper.update(sql,params);
    }

	
    public List<Userinfo> userinfoAll()
    {
    	List<Userinfo> userinfos=new ArrayList<Userinfo>();
    	String sql="SELECT userinfo.* FROM userinfo";
    	Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next())
			{
			Userinfo userinfo=new Userinfo();
				userinfo.setId(rs.getInt("id"));
				userinfo.setNickname(rs.getString("nickname"));
				userinfo.setGender(rs.getString("gender"));
				userinfo.setPhone(rs.getString("phone"));
				userinfo.setBirthday(rs.getDate("birthday"));
				userinfo.setJoinDate(rs.getTimestamp("join_date"));
				userinfo.setRealName(rs.getString("real_name"));
				userinfos.add(userinfo);
			}		
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return userinfos;
    }
    
    

    
    
    public void pay(ShopCar shopCar,int userId)
    {	
		
    	for (int i = 0; shopCar!=null&&i <shopCar.getScenics().size(); i++) {
    		int scenicId=shopCar.getScenics().get(i).getScenic().getId();
    		List<Integer> price=new ArrayList<Integer>();
    		if(shopCar.getScenics().get(i).getTickitChild()!=null) {
    			int tcN=shopCar.getScenics().get(i).getTickitChild().getTickitCount();
    			price.add(tcN);
    		}
    		if(shopCar.getScenics().get(i).getTickitMan()!=null) {
    		int tmN=shopCar.getScenics().get(i).getTickitMan().getTickitCount();
    		price.add(tmN);
    		}
    		if(shopCar.getScenics().get(i).getTickitOld()!=null) {
    		int toN=shopCar.getScenics().get(i).getTickitOld().getTickitCount();
    		price.add(toN);
    		}
    		
    		for (int j = 0; price!=null&&j < price.size(); j++) {
				
			
    	String sql="INSERT INTO pay(user_id,tickit_type_id,num,scenic_id)VALUES(?,?,?,?)";
    	Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		try {
			ps=conn.prepareStatement(sql);		
			ps.setObject(1, userId);
			ps.setObject(2, i+1);
			ps.setObject(3, price.get(j));
			ps.setObject(4, scenicId);
			ps.executeUpdate();
			} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbHelper.close( ps, conn);
		}
    }
    	}
    }
    
    

    public void insertCollection(int userinfoid,int scenicid)
    {
    	String sql="INSERT INTO collection (scenic_id,userinfo_id)VALUES(?,?)";
    	ArrayList params=new ArrayList();
    	params.add(scenicid);
    	params.add(userinfoid);
    	DbHelper.update(sql,params);	
    }
    public List<Scenic>  findByCollection(int id)
    {
    	ArrayList<Scenic> scenics=new ArrayList<Scenic>();
    	Scenic scenic=null;
    	String sql="SELECT scenic.*,collection.*,userinfo.id FROM collection LEFT JOIN scenic ON collection.scenic_id=scenic.id LEFT JOIN userinfo ON collection.userinfo_id=userinfo.id WHERE collection.userinfo_id=? and collection.enable=1";
    	Connection conn=DbHelper.getConn();
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1,id);
			rs=ps.executeQuery();
			while(rs.next())
			{
				scenic=new Scenic();
				scenic.setIntroPic(rs.getString("intro_pic"));
				scenic.setName(rs.getString("name"));
				scenic.setId(rs.getInt("id"));
				scenics.add(scenic);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DbHelper.close(rs, ps, conn);
		}
		return scenics;
    }
    public void deletedCollection(int id)
	{
		String sql="UPDATE  collection set enable=0 WHERE scenic_id=?" ;
		ArrayList params=new ArrayList();
		params.add(id);
		DbHelper.update(sql, params);
		
	}

}
